﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;

namespace Eacademy.Library.Helpers
{
    public class DbFactory
    {

        public Database database { get; set; }

        private MySqlConnection _connection;

        private const string CONNECTION_STRING_NAME = "mysqlConnectionString";

        private static string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings[CONNECTION_STRING_NAME].ToString();
            }
        }

        public MySqlConnection Connection
        {
            get
            {
                if (_connection == null)
                {
                    _connection = new MySqlConnection(ConnectionString);
                }
                return _connection;
            }
            protected set
            {
                if (value is MySqlConnection || value != null)
                {
                    _connection = value;
                }
                else
                {
                    _connection = new MySqlConnection(ConnectionString);
                }
            }
        }


        public DbFactory()
        {
            //creates a database object
            database = DatabaseFactory.CreateDatabase();
        }


        //For Insert/Update/Delete
        public void ExecuteNonQuery(DbCommand comm)
        {
            if (comm == null || string.IsNullOrEmpty(comm.CommandText))
            {
                return;
            }
            else
            {
                if (database == null)
                {
                    database = DatabaseFactory.CreateDatabase();
                }

                //create a db connection
                using (DbConnection conn = database.CreateConnection())
                {
                    //open connection and begin transaction
                    conn.Open();
                    comm.Connection = conn;
                    //DbTransaction trans = conn.BeginTransaction();
                    DbTransaction trans = comm.Connection.BeginTransaction();

                    //perform query operation
                    try
                    {
                        comm.ExecuteNonQuery();
                        //_database.ExecuteNonQuery(comm);

                        //commit changes
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        //rollback changes if error occurs
                        trans.Rollback();
                        throw ex;
                    }
                    finally
                    {
                        //close and dispose connnection and transaction objects
                        trans.Dispose();
                        conn.Close();
                        conn.Dispose();
                    }
                }
                return;
            }
        }

        //For Select
        public DataSet ExecuteQuery(DbCommand comm)
        {
            if (comm == null || string.IsNullOrEmpty(comm.CommandText))
            {
                return null;
            }
            else
            {

                if (database == null)
                {
                    database = DatabaseFactory.CreateDatabase();
                }

                try
                {
                    DataSet ds = database.ExecuteDataSet(comm);
                    return ds;
                }
                catch (Exception ex)
                {
                    //
                    throw ex;
                }
            }
        }

        public long GetRecordCount(DbCommand comm)
        {
            if (comm == null || string.IsNullOrEmpty(comm.CommandText))
            {
                return -1;
            }

            if (database == null)
            {
                database = DatabaseFactory.CreateDatabase();
            }

            long count = 0;
            try
            {
                count = Convert.ToInt64(database.ExecuteScalar(comm));
                return count;
            }
            catch (Exception ex)
            {
                //log
                throw ex;
            }
        }

    }
}